# -*- coding:utf-8 -*-#
# -------------------------------------------------------------------------
# ProjectName:       zhcx-api-master
# FileName:          DuMysql.py
# Author:            xun
# Date:              2023/8/31 16:13
# Description:
# --------------------------------------------------------------------------
import pymysql
from prettytable import PrettyTable
from datetime import datetime
class DuMysql:
    '''
    [db]
    host=139.159.227.93
    user=reader
    password=5vv1YDXCR3W8ngad#
    database=irobotbox_finance
    port=3306
    '''
    def __init__(self):
        self.host = '139.159.227.93'
        self.user = 'reader'
        self.password = '5vv1YDXCR3W8ngad#'
        self.database = 'irobotbox_finance'
        self.port = 3306
        self.table=PrettyTable()
        self.connect=pymysql.connect(host=self.host,user=self.user,password=self.password,database=self.database,port=self.port,charset='gbk')
        self.cousor=self.connect.cursor()

    #搜索回款对比发货的数据
    def jiesuan(self,totalType,WareHouseType,date:str,orderSourceName=None):
        '''
        totalType:1、已结算；3、未结算，
        WareHouseType:1、自发货；3、FBA
        date可以输入<14、<30、<60、<90、>90
        '''
        if date == '<14':
            date1='\'2023-10-17 23:59:59\''
            date2='\'2023-10-31 23:59:59\''
        elif date == '<30':
            date1='\'2023-10-01 23:59:59\''
            date2='\'2023-10-17 23:59:59\''
        elif date == '<60':
            date1='\'2023-09-01 23:59:59\''
            date2='\'2023-10-01 23:59:59\''
        elif date == '<90':
            date1='\'2023-08-02 23:59:59\''
            date2='\'2023-09-01 23:59:59\''
        elif date == '>90':
            date1='\'2023-01-01 00:00:00\''
            date2='\'2023-08-02 23:59:59\''
        MatchType = 0
        if totalType == 1:
            totalType = "PaymentExchangeTotalAmount"
            MatchType = 1
        else:
            totalType = "ShipmentBuyPriceTotal"
        if orderSourceName:
            sql="select 	count(*),CAST(sum({0}) AS CHAR)  from irobotbox_finance.ifc_payment_shipment_comparison_amazon " \
                "where	MatchType ={1} and WareHouseType ={2} and ShipmentOperateTime > {3} and ShipmentOperateTime <= {4} and OrderSourceName in (\'{5}\')".format(totalType,MatchType,WareHouseType,date1,date2,orderSourceName)
        else:
            sql = "select 	count(*),CAST(sum({0}) AS CHAR)  from irobotbox_finance.ifc_payment_shipment_comparison_amazon " \
                  "where	MatchType ={1} and WareHouseType ={2} and ShipmentOperateTime > {3} and ShipmentOperateTime <= {4}".format(totalType,MatchType,WareHouseType, date1, date2)
        # print(sql)
        self.cousor.execute(sql)
        return self.cousor.fetchall()

    def get_data(self,OrderSourceName=None):
        '''查询总表数据'''
        self.table=PrettyTable(["类别","发货方式","合计(未回款天数所有数据合计)","<=14天","15天至30天","31天至60天","61天至90天","90天+"])
        '''查看指定来源渠道的所有数据'''
        json1={'未结算':{"FBA":{},"自发货":{}}}
        #已结算、FBA、<=14天的数据为
        tuple1=self.jiesuan(1,3,"<14",OrderSourceName)[0]
        tuple2=self.jiesuan(1,1,"<14",OrderSourceName)[0]

        # 未结算、FBA、<=14天的数据为
        tuple3 = self.jiesuan(3, 3, "<14", OrderSourceName)[0]
        tuple4 = self.jiesuan(3, 1, "<14", OrderSourceName)[0]

        num1="*"
        # print(num1*100)
        # 已结算、FBA、<=30天的数据为
        tuple5 = self.jiesuan(1, 3, "<30", OrderSourceName)[0]
        tuple6 = self.jiesuan(1, 1, "<30", OrderSourceName)[0]

        # 未结算、FBA、<=30天的数据为
        tuple7 = self.jiesuan(3, 3, "<30", OrderSourceName)[0]
        tuple8 = self.jiesuan(3, 1, "<30", OrderSourceName)[0]

        # print(num1 * 100)
        # 已结算、FBA、<=60天的数据为
        tuple9 = self.jiesuan(1, 3, "<60", OrderSourceName)[0]
        tuple10 = self.jiesuan(1, 1, "<60", OrderSourceName)[0]

        # 未结算、FBA、<=60天的数据为
        tuple11 = self.jiesuan(3, 3, "<60", OrderSourceName)[0]
        tuple12 = self.jiesuan(3, 1, "<60", OrderSourceName)[0]

        # print(num1 * 100)
        # 已结算、FBA、<=90天的数据为
        tuple13 = self.jiesuan(1, 3, "<90", OrderSourceName)[0]
        tuple14 = self.jiesuan(1, 1, "<90", OrderSourceName)[0]

        # 未结算、FBA、<=90天的数据为
        tuple15 = self.jiesuan(3, 3, "<90", OrderSourceName)[0]
        tuple16 = self.jiesuan(3, 1, "<90", OrderSourceName)[0]

        # print(num1 * 100)
        # 已结算、FBA、>90天的数据为
        tuple17 = self.jiesuan(1, 3, ">90", OrderSourceName)[0]
        tuple18 = self.jiesuan(1, 1, ">90", OrderSourceName)[0]

        # 未结算、FBA、>90天的数据为
        tuple19 = self.jiesuan(3, 3, ">90", OrderSourceName)[0]
        tuple20 = self.jiesuan(3, 1, ">90", OrderSourceName)[0]

        #总合计
        # 合计金额计算
        self.table.add_row(['','', '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple1[0] + tuple2[0]+tuple3[0] + tuple4[0]+tuple5[0] + tuple6[0]+tuple7[0] + tuple8[0]+tuple9[0] + tuple10[0]+tuple11[0] + tuple12[0]+tuple13[0] + tuple14[0]+tuple15[0] + tuple16[0]+tuple17[0] + tuple18[0]+tuple19[0] + tuple20[0],
                                                                  round(float(tuple1[1]) + float(tuple2[1]) + float(tuple3[1]) + float(tuple4[1])+
                                                                        float(tuple5[1]) + float(tuple6[1])+float(tuple7[1]) + float(tuple8[1])+
                                                                        float(tuple9[1]) + float(tuple10[1])+float(tuple11[1]) + float(tuple12[1])+
                                                                        float(tuple13[1]) + float(tuple14[1])+float(tuple15[1]) + float(tuple16[1])+
                                                                        float(tuple17[1]) + float(tuple18[1])+float(tuple19[1]) + float(tuple20[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple1[0] + tuple2[0]+tuple3[0] + tuple4[0],
                                                                          round(float(tuple1[1]) + float(tuple2[1])+float(tuple3[1]) + float(tuple4[1]),2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple5[0] + tuple6[0]+tuple7[0] + tuple8[0],
                                                             round(float(tuple5[1]) + float(tuple6[1])+float(tuple7[1]) + float(tuple8[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple9[0] + tuple10[0]+tuple11[0] + tuple12[0],
                                                             round(float(tuple9[1]) + float(tuple10[1])+float(tuple11[1]) + float(tuple12[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple13[0] + tuple14[0]+tuple15[0] + tuple16[0],
                                                             round(float(tuple13[1]) + float(tuple14[1])+float(tuple15[1]) + float(tuple16[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple17[0] + tuple18[0]+tuple19[0] + tuple20[0],
                                                             round(float(tuple17[1]) + float(tuple18[1])+float(tuple19[1]) + float(tuple20[1]), 2))])
        #已结算、FBA仓的发货数据
        self.table.add_row(['已结算','FBA','订单数量：{0}\n结算金额(RMB):{1}'.format(tuple1[0]+tuple5[0]+tuple9[0]+tuple13[0]+tuple17[0],
                                                                         round(float(tuple1[1])+float(tuple5[1])+float(tuple9[1])+float(tuple13[1])+float(tuple17[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple1[0],
                                                                    round(float(tuple1[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple5[0],
                                                                    round(float(tuple5[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple9[0],
                                                             round(float(tuple9[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple13[0],
                                                             round(float(tuple13[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple17[0],
                                                             round(float(tuple17[1]), 2))])
        self.table.add_row(['已结算','自发货','订单数量：{0}\n结算金额(RMB):{1}'.format(tuple2[0]+tuple6[0]+tuple10[0]+tuple14[0]+tuple18[0],
                                                                         round(float(tuple2[1])+float(tuple6[1])+float(tuple10[1])+float(tuple14[1])+float(tuple18[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple2[0],
                                                                    round(float(tuple2[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple6[0],
                                                             round(float(tuple6[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple10[0],
                                                             round(float(tuple10[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple14[0],
                                                             round(float(tuple14[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple18[0],
                                                             round(float(tuple18[1]), 2))])
        # 合计金额计算
        self.table.add_row(['已结算', '合计', '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple1[0]+tuple2[0]+tuple5[0]+tuple6[0]+tuple9[0]+tuple10[0]+tuple13[0]+tuple14[0]+tuple17[0]+tuple18[0],
                                                                          round(float(tuple1[1]) + float(tuple2[1])+
                                                                                float(tuple5[1])+float(tuple6[1])+
                                                                                float(tuple9[1])+float(tuple10[1])+
                                                                                float(tuple13[1])+float(tuple14[1])+
                                                                                float(tuple17[1])+float(tuple18[1]),2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple1[0]+tuple2[0],
                                                                           round(float(tuple1[1])+float(tuple2[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple5[0]+tuple6[0],
                                                             round(float(tuple5[1])+float(tuple6[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple9[0]+tuple10[0],
                                                             round(float(tuple9[1])+float(tuple10[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple13[0]+tuple14[0],
                                                             round(float(tuple13[1])+float(tuple14[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple17[0]+tuple18[0],
                                                             round(float(tuple17[1])+float(tuple18[1]), 2))])

        self.table.add_row(['未结算', 'FBA','订单数量：{0}\n结算金额(RMB):{1}'.format(tuple3[0]+tuple7[0]+tuple11[0]+tuple15[0]+tuple19[0],
                                                                          round(float(tuple3[1])+float(tuple7[1])+float(tuple11[1])+float(tuple15[1])+float(tuple19[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple3[0],
                                                                           round(float(tuple3[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple7[0],
                                                             round(float(tuple7[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple11[0],
                                                             round(float(tuple11[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple15[0],
                                                             round(float(tuple15[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple19[0],
                                                             round(float(tuple19[1]), 2))])
        self.table.add_row(['未结算', '自发货','订单数量：{0}\n结算金额(RMB):{1}'.format(tuple4[0]+tuple8[0]+tuple12[0]+tuple16[0]+tuple20[0],
                                                                          round(float(tuple4[1])+float(tuple8[1])+float(tuple12[1])+float(tuple16[1])+float(tuple20[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple4[0],
                                                                           round(float(tuple4[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple8[0],
                                                             round(float(tuple8[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple12[0],
                                                             round(float(tuple12[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple16[0],
                                                             round(float(tuple16[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple20[0],round(float(tuple20[1]), 2))])
        # 未结算合计金额
        self.table.add_row(['未结算', '合计', '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple3[0]+tuple4[0]+tuple7[0]+tuple8[0]+tuple11[0]+tuple12[0]+tuple15[0]+tuple16[0]+tuple19[0]+tuple20[0],
                                                                          round(float(tuple3[1]) + float(tuple4[1])+
                                                                                float(tuple7[1])+float(tuple8[1])+
                                                                                float(tuple11[1])+float(tuple12[1])+
                                                                                float(tuple15[1])+float(tuple16[1])+
                                                                                float(tuple19[1])+float(tuple20[1]),2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple3[0]+tuple4[0],
                                                                           round(float(tuple3[1])+float(tuple4[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple7[0]+tuple8[0],
                                                             round(float(tuple7[1])+float(tuple8[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple11[0]+tuple12[0],
                                                             round(float(tuple11[1])+float(tuple12[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple15[0]+tuple16[0],
                                                             round(float(tuple15[1])+float(tuple16[1]), 2)),
                            '订单数量：{0}\n结算金额(RMB):{1}'.format(tuple19[0]+tuple20[0],
                                                             round(float(tuple19[1])+float(tuple20[1]), 2))])
        print(self.table)

    def get_Detailed(self,OrderSourceName,ClientOrderCode):
        '''根据来源渠道和平台订单号查询发货仓库、订单成本、结算金额'''
        #来源渠道转换成id
        sql0="select OrderSourceID from irobotbox_finance.ifc_payment_shipment_comparison_amazon where OrderSourceName=\'{0}\' and ClientOrderCode = \'{1}\'".format(OrderSourceName,ClientOrderCode)
        self.cousor.execute(sql0)
        OrderSourceID=self.cousor.fetchone()
        #查询总成本
        sql1="select SUM(ManagementTotalAccount) from irobotbox_finance.ifc_shipment_amazon where OrderSourceID=\'{0}\' and ClientOrderCode=\'{1}\' " \
             "and BusinessType<>6 and OperateTime >= '{2}-01-01 00:00:00' and OperateTime < '{3}-{4}-01 00:00:00'".format(OrderSourceID[0],ClientOrderCode,datetime.today().year,datetime.today().year,datetime.today().month)
        # print(sql1)
        self.cousor.execute(sql1)
        data1=self.cousor.fetchone()
        #查询结算总金额
        try:
            sql2="select SUM(x.ProductSales+x.ShippingCredits+x.GiftWrapCredits+x.PromotionalRebates+x.SalesTaxCollected+" \
                 "x.CostOfPints+AdsPayment+x.SellingFees+x.FbaFees+x.FBAStorageFee+x.FBAInboundFee+x.FBARemovalFee+" \
                 "x.OtherTransactionFees+x.OtherInCome +x.CouponFee + x.LongTermStorageFee) as Account,x.PostedDate " \
                 "from irobotbox_finance.ifc_payment_amazon x where x.OrderSourceID=\'{0}\' and x.AmazonOrderId=\'{1}\' and x.BussinessType = 1 and x.PostedDate < '{2}-{3}-01 00:00:00' ".format(OrderSourceID[0],ClientOrderCode,datetime.today().year,datetime.today().month)
            self.cousor.execute(sql2)
            data2=self.cousor.fetchall()
            amount2 = data2[0][0]
            # 获取当前时间
            dt = datetime.today()
            if dt.month  <= data2[0][1].month: #当前订单无结算数据时,会在调用month时报错,从而执行except中的赋值操作
                amount2 = 0
        except:
            amount2=0
        return float(data1[0]),float(amount2)

    def close(self):
        self.cousor.close()
        self.connect.close()



if __name__ == '__main__':
    dm=DuMysql()
    # # print(dm.jiesuan(1,3,"<14",'Amazon-Z01486-DE'))
    dm.get_data() #回款对比发货看板
    # table=PrettyTable()
    # for i in range(10):
    #       table.add_column(str(i),['hello'])
    # print(table)
    # data1=dm.get_Detailed('Amazon-Z01726-US','111-6682453-8985834') #Amazon-Z01718-BR	701-1774281-9644267
    # data1=dm.get_Detailed('Amazon-Z01453-SE','402-7330122-6190701')
    # print(data1,type(data1))